/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.Date;

import org.springframework.dao.EmptyResultDataAccessException;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;
import org.apache.commons.lang.StringUtils;

import com.feib.stms.dao.PreDesignatedAccountDao;
import com.feib.stms.dao.jdbc.rowmapper.PreDesignatedAccountRowMapper;
import com.feib.stms.model.AuditEntity;
import com.feib.stms.model.PreDesignatedAccount;

/**
 * @author Karl Chen
 *
 */
@Repository("preDesignatedAccountDao")
public class PreDesignatedAccountJdbcDao extends BaseJdbcDao implements PreDesignatedAccountDao 
{
	private OracleSequenceMaxValueIncrementer sequence;
	
	private SimpleJdbcInsert preDesignatedAccountJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "PRE_DESIGNATED_ACCOUNT_PK_SEQ");
		this.preDesignatedAccountJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.preDesignatedAccountJdbcInsert.withTableName("PRE_DESIGNATED_ACCOUNT")
			.usingColumns("PRE_DESIGNATED_ACCOUNT_PK", "GROUP_PK", "SECURITIES_PK", "BANK_BRACNCH_NO_1", "ACCOUNT_NO_H1"
				, "ACCOUNT_NO_R1", "ACCOUNT_NO_P1", "BANK_BRACNCH_NO_2", "ACCOUNT_NO_H2", "ACCOUNT_NO_R2"
				, "ACCOUNT_NO_P2", "IS_DELETED" ,"CREATED_DATE" ,"CREATED_BY" ,"UPDATED_DATE" ,"UPDATED_BY");
	}
	
	/**
	 * 根據分公司代號查詢約定帳號
	 * @param securitiesNo
	 * @return
	 */
	public PreDesignatedAccount getPreDesignatedAccountBySecuritiesNo(String securitiesNo, String bankBracnchNo) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("select pda.* from PRE_DESIGNATED_ACCOUNT pda , SECURITIES s ");
		sql.append("where s.securities_pk = pda.SECURITIES_PK ");
		sql.append(" and pda.is_deleted = 0 ");
				
		if(StringUtils.isNotEmpty(securitiesNo)){
			sql.append(" and s.securities_no = :securities_no ");
			parameters.addValue("securities_no",  securitiesNo, Types.VARCHAR);
		}
		
		if(StringUtils.isNotEmpty(bankBracnchNo)){
			sql.append("AND( PDA.BANK_BRACNCH_NO_1= :bankBracnchNo1 OR PDA.BANK_BRACNCH_NO_2 =	:bankBracnchNo2 )");
			parameters.addValue("bankBracnchNo1",  bankBracnchNo, Types.VARCHAR);
			parameters.addValue("bankBracnchNo2",  bankBracnchNo, Types.VARCHAR);
		}
				
		
		try{
			return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new PreDesignatedAccountRowMapper(this), parameters);
		}catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	/**
	 * 根據分公司代號及群組Pk查詢約定帳號
	 * @param securitiesNo
	 * @param groupPk
	 * @return
	 */
	public PreDesignatedAccount getPreDesignatedAccountBySecuritiesNoAndGroupPk(String securitiesNo, Long groupPk, String bankBracnchNo) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("select pda.* from PRE_DESIGNATED_ACCOUNT pda , SECURITIES s ");
		sql.append("where s.securities_pk = pda.SECURITIES_PK ");
		sql.append(" and pda.is_deleted = 0 ");
		if(StringUtils.isNotEmpty(securitiesNo)){
			sql.append(" and s.securities_no = :securities_no ");
			parameters.addValue("securities_no",  securitiesNo, Types.VARCHAR);
		}
		if(StringUtils.isNotEmpty(bankBracnchNo)){
			sql.append("AND ( PDA.BANK_BRACNCH_NO_1= :bankBracnchNo1 OR PDA.BANK_BRACNCH_NO_2 =	:bankBracnchNo2 )");
			parameters.addValue("bankBracnchNo1",  bankBracnchNo, Types.VARCHAR);
			parameters.addValue("bankBracnchNo2",  bankBracnchNo, Types.VARCHAR);
			
		}
		if(null != groupPk && 0L != groupPk){
			sql.append(" and s.group_pk = :group_pk");
			parameters.addValue("group_pk",groupPk, Types.NUMERIC);
		}
				
		return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new PreDesignatedAccountRowMapper(this), parameters);
	}

	/**
	 * 新增券商約定帳號
	 * @return
	 */
	public int insert(PreDesignatedAccount preDesignatedAccount){
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		Long pre_Designated_Account_Pk = new Long(sequence.nextLongValue());
		parameters.addValue("PRE_DESIGNATED_ACCOUNT_PK", pre_Designated_Account_Pk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  preDesignatedAccount.getGroup_Pk(), Types.NUMERIC);
		parameters.addValue("SECURITIES_PK",  preDesignatedAccount.getSecurities_Pk(), Types.NUMERIC);
		
		parameters.addValue("BANK_BRACNCH_NO_1",  preDesignatedAccount.getBankBracnchNo1(), Types.VARCHAR);
		parameters.addValue("ACCOUNT_NO_H1",  preDesignatedAccount.getAccount_No_H1(), Types.VARCHAR);
		parameters.addValue("ACCOUNT_NO_R1",  preDesignatedAccount.getAccount_No_R1(), Types.VARCHAR);
		parameters.addValue("ACCOUNT_NO_P1",  preDesignatedAccount.getAccount_No_P1(), Types.VARCHAR);
		
		parameters.addValue("BANK_BRACNCH_NO_2",  preDesignatedAccount.getBankBracnchNo2(), Types.VARCHAR);
		parameters.addValue("ACCOUNT_NO_H2",  preDesignatedAccount.getAccount_No_H2(), Types.VARCHAR);
		parameters.addValue("ACCOUNT_NO_R2",  preDesignatedAccount.getAccount_No_R2(), Types.VARCHAR);		
		parameters.addValue("ACCOUNT_NO_P2",  preDesignatedAccount.getAccount_No_P2(), Types.VARCHAR);
		
		parameters.addValue("IS_DELETED",  preDesignatedAccount.getIs_Deleted() == null ? 0 : preDesignatedAccount.getIs_Deleted(), Types.NUMERIC);
		
		parameters.addValue("CREATED_DATE", preDesignatedAccount.getCreatedDate(), Types.TIMESTAMP);
		parameters.addValue("CREATED_BY",  preDesignatedAccount.getCreatedBy(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  preDesignatedAccount.getUpdatedDate(), Types.TIMESTAMP);		
		parameters.addValue("UPDATED_BY",  preDesignatedAccount.getUpdatedBy(), Types.VARCHAR);
		 
		
		int updateCounts = preDesignatedAccountJdbcInsert.execute(parameters);		

		if (updateCounts > 0)
			preDesignatedAccount.setPre_Designated_Account_Pk(pre_Designated_Account_Pk);
		
		return updateCounts;
	}
	
	/**
	 * 更新券商約定帳號
	 * @param preDesignatedAccountPk 更新前券商約定帳號資料的主鍵 
	 * @param auditEntity 更新者的相關信息
	 * @return
	 */
	public int update(Long preDesignatedAccountPk,AuditEntity auditEntity){
		StringBuffer sql = new StringBuffer();
		//檢查更新前券商約定帳號資料的主鍵.
		if(null == preDesignatedAccountPk || 0L == preDesignatedAccountPk)return 0;
		//設定更新人員信息
		String updateBy;
		Date updateDate;
		//檢查并取得更新人員信息.
		if(auditEntity != null){			
			updateBy = auditEntity.getUpdatedBy();
			updateDate = auditEntity.getUpdatedDate();
		}else{
			return 0;
		}		
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("UPDATE PRE_DESIGNATED_ACCOUNT SET IS_DELETED = 1 ");		
		sql.append(" ,UPDATED_BY = :UPDATED_BY, UPDATED_DATE = :UPDATED_DATE ");
		sql.append(" WHERE PRE_DESIGNATED_ACCOUNT_PK = :PRE_DESIGNATED_ACCOUNT_PK");
		
		parameters.addValue("PRE_DESIGNATED_ACCOUNT_PK",  preDesignatedAccountPk, Types.NUMERIC);
		parameters.addValue("UPDATED_BY", updateBy, Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  updateDate, Types.TIMESTAMP);		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
}
